USE CairoSitters
 

--Lookups--
CREATE TABLE AccountTypes
(
	AccountTypeID	INT	IDENTITY(1,1),
	AName			varchar(50),
	Deleted			bit,

	CONSTRAINT pk_AccountTypes PRIMARY KEY (AccountTypeID)
)


--CREATE TABLE SittingTime
--(
--	STID	INT IDENTITY(1,1),
--	STName	varchar(50),
--	Deleted bit,

--	CONSTRAINT pk_SittingTime PRIMARY KEY (STID)
--)


--CREATE TABLE HoursPlan
--(
--	HPID		INT IDENTITY(1,1),
--	HPName		varchar(50),
--	HoursCount	INT,
--	Deleted		bit,

--	CONSTRAINT pk_HoursPlan PRIMARY KEY (HPID)
--)



CREATE TABLE EmergencyContacts
(
	ECID			INT IDENTITY (1,1),
	ECName          varchar(50),
	MobileNo		varchar(50),
	PhoneNo			varchar(50),
	Deleted			bit,
	

	CONSTRAINT pk_EmergencyContacts PRIMARY KEY (ECID)
)

CREATE TABLE DayHours
(
	DHID	INT IDENTITY(1,1),
	DHName	varchar(50),
	DHSH	INTEGER,
	DHEH	INTEGER,

	CONSTRAINT pk_DayHours PRIMARY KEY (DHID)
)


CREATE TABLE Employers
(
	EmployerID	INT IDENTITY(1,1),
	EName		varchar(50),
	Deleted		bit,

	CONSTRAINT pk_Employers PRIMARY KEY (EmployerID)
)


CREATE TABLE SchoolsUniversities
(
	SUID		INT IDENTITY (1,1),
	SUName		varchar(50),
	Deleted		bit,

	CONSTRAINT pk_SchoolsUniversities PRIMARY KEY (SUID)
)

CREATE TABLE Languages
(
	LanguageID	INT IDENTITY(1,1),
	BLanguage	varchar(50),
	Deleted		bit,

	CONSTRAINT pk_Languages PRIMARY KEY (LanguageID)
)


CREATE TABLE Cities
(
	CityID		INT IDENTITY(1,1),
	CName		varchar(50),
	Deleted		bit,

	CONSTRAINT pk_Cities PRIMARY KEY (CityID)
)


CREATE TABLE Nationalities
(
	NationalityID	INT IDENTITY(1,1),
	NName			varchar(50),
	Deleted			bit,

	CONSTRAINT pk_Nationalities PRIMARY KEY (NationalityID)
)


CREATE TABLE HobbiesSkills
(
	HSID	INT IDENTITY(1,1),
	HSName	varchar(50),
	Deleted bit,

	CONSTRAINT pk_HobbiesSkills PRIMARY KEY (HSID)
)

CREATE TABLE Plans
(
	PlanID				INT IDENTITY(1,1),
	Category			varchar(50),
	PackageHours		INTEGER,
	PricePerHour		INTEGER,
	PackageDiscount		FLOAT(24),
	Deleted				bit,

	CONSTRAINT pk_Plans PRIMARY KEY (PlanID)
)
	
--Lookups End--

--First Level--

CREATE TABLE Accounts
(
	AccountID		INT IDENTITY(1,1),
	UserName		varchar(50),
	Pass_word		varchar(50),
	AccountTypeID	INTEGER,
	DateCreated		AS GETDATE(),
	Interviewed     bit DEFAULT 0,
	Approved		bit,
	Deleted			bit,

	CONSTRAINT pk_Accounts PRIMARY KEY (AccountID),
	FOREIGN KEY (AccountTypeID) REFERENCES AccountTypes 
)

--CREATE TABLE SittingPlan
--(
--	SPID				INT IDENTITY(1,1),
--	ChildrenNo			INTEGER,
--	PricePerHour		INTEGER,
--	STID				INTEGER,
--	DiscountPercentage	INTEGER,
--	Deleted				bit,

--	CONSTRAINT pk_SittingPlan PRIMARY KEY (SPID),
--	FOREIGN KEY (STID) REFERENCES SittingTime 
--)

--First Level End--

--Other Levels--

CREATE TABLE Parents
(
	ParentID		INT IDENTITY(1,1),
	AccountID		INTEGER,
	ParentName		varchar(50),
	EMail			varchar(50),
	DoB				date,
	Gender          char(1),
	CellNo			varchar(50),
	PhoneNo			varchar(50),
	Employment		varchar(50),
	StreetAddress	varchar(50),
	Area			varchar(50),
	PostalCode		varchar(50),
	CityID			INTEGER,
	Deleted			bit,

	CONSTRAINT pk_Parents PRIMARY KEY (ParentID),
	FOREIGN KEY (AccountID) REFERENCES Accounts
)


CREATE TABLE Children
(
	ChildID				INT IDENTITY(1,1),
	CName				varchar(50),
	AccountID			INTEGER,
	DoB					date,
	Gender				char(1),
	HealthConditions	varchar(50),
	Schools				varchar(50),
	Grade				varchar(50),
	Deleted				bit,

	CONSTRAINT pk_Children PRIMARY KEY (ChildID),
	FOREIGN KEY (AccountID) REFERENCES Accounts 
)


CREATE TABLE BabySitters
(
	BSID					INT IDENTITY (1,1),
	BSName					varchar(50),
	AccountID				INTEGER,
	DoB						datetime,
	EMail					varchar(50),
	CellNo					varchar(50),
	PhoneNo					varchar(50),
	NationalityID			INTEGER,
	Gender					char(1),
	BS_Address				varchar(50),
	Area					varchar(50),
	PostalCode				varchar(50),
	CityID					INTEGER,
	Experience				varchar(50),
	Essay					varchar (MAX),
	Allergies				nvarchar(max),
    Petconcern				nvarchar(max),
    Trained					bit DEFAULT 0,
    WorkStatus				INTEGER,
    PaidTrainingInAdvance	bit DEFAULT 0,
    PaidTrainingInTotal		bit DEFAULT 0,
    Deleted					bit,

	CONSTRAINT pk_BabySitters PRIMARY KEY (BSID),
	FOREIGN KEY (CityID) REFERENCES Cities ,
	FOREIGN KEY (NationalityID) REFERENCES Nationalities ,
	FOREIGN KEY (AccountID) REFERENCES Accounts 
)


--CREATE TABLE FullSittingPlan
--(
--	FSPID				INT IDENTITY(1,1),
--	SPID				INTEGER,
--	HPID				Integer,
--	DiscountPercentage	INTEGER,

--	CONSTRAINT pk_FullSittingPlan PRIMARY KEY (FSPID),
--	FOREIGN KEY (SPID) REFERENCES SittingPlan ,
--	FOREIGN KEY (HPID) REFERENCES HoursPlan 
--)

CREATE TABLE BSSchedule
(
	BSSID		INT IDENTITY (1,1),
	BSID		INTEGER,
	S_Day		varchar(50),
	Deleted		bit,

	CONSTRAINT pk_BSSchedule PRIMARY KEY (BSSID),
	FOREIGN KEY (BSID) REFERENCES BabySitters 
)

CREATE TABLE DH_BSS
(
	DHBSSID		INT IDENTITY(1,1),
	BSSID		INTEGER,
	DHID		INTEGER,

	CONSTRAINT pk_DH_BSS PRIMARY KEY (DHBSSID),
	FOREIGN KEY (BSSID) REFERENCES BSSchedule ,
	FOREIGN KEY (DHID) REFERENCES DayHours 
)



CREATE TABLE BSEmploymentBackground
(
	BSEBGID		INT IDENTITY (1,1),
	BSID		INTEGER,
	EDate		varchar(50),
	Duties		varchar(max),
	EmployerID	INTEGER,

	CONSTRAINT pk_BSEmploymentBackground PRIMARY KEY (BSEBGID),
	FOREIGN KEY (BSID) REFERENCES BabySitters ,
	FOREIGN KEY (EmployerID) REFERENCES Employers 
)


CREATE TABLE BSEducationalBackground
(
	BSEBID			INT IDENTITY (1,1),
	BSID			INTEGER,
	SUID			INTEGER,
	YearsCompleted	INTEGER,
	GraduationYear	INT,

	CONSTRAINT pk_BSEducationalBackground PRIMARY KEY (BSEBID),
	FOREIGN KEY (BSID) REFERENCES BabySitters ,
	FOREIGN KEY (SUID) REFERENCES SchoolsUniversities 
)


CREATE TABLE BSWorkConditions
(
	BSWCID			   INT IDENTITY (1,1),
	BSID			   INTEGER,
	clubs              bit,
    cinemas            bit,
    otherEvents        bit,
    withparentsathome  bit,
    withparentsatclub  bit,
    withmaidathome     bit,
    withmaidatclub     bit,
    aloneathome        bit,
    aloneatclub        bit,
    daycaretwosix      bit,
    daycaresixtwelve   bit,
    nightcare          bit,
    workoffsched       bit,
    hoursaweek         int,
    hoursaday          int,

	CONSTRAINT pk_BSWorkConditions PRIMARY KEY (BSWCID),
	FOREIGN KEY (BSID) REFERENCES BabySitters
)


CREATE TABLE EmergencyContacts_Accounts
(
	ECAID		INT IDENTITY (1,1),
	AccountID	INTEGER,
	ECID		INTEGER,
	Relationship	varchar(50),

	CONSTRAINT pk_EmergencyContacts_Accounts PRIMARY KEY (ECAID),
	FOREIGN KEY (AccountID) REFERENCES Accounts ,
	FOREIGN KEY (ECID) REFERENCES EmergencyContacts 
)


--CREATE TABLE SittingAssignment
--(
--	SAID		INT IDENTITY(1,1),
--	FSPID		INTEGER,
--	DHBSSID		INTEGER,

--	CONSTRAINT pk_SittingAssignment PRIMARY KEY (SAID),
--	FOREIGN KEY (FSPID) REFERENCES FullSittingPlan ,
--	FOREIGN KEY (DHBSSID) REFERENCES DH_BSS 
--)



--CREATE TABLE Children_SittingAssignment
--(
--	CSAID		INT IDENTITY(1,1),
--	SAID		INTEGER,
--	ChildID		INTEGER,

--	CONSTRAINT pk_Children_SittingAssignment PRIMARY KEY (CSAID),
--	FOREIGN KEY (SAID) REFERENCES SittingAssignment ,
--	FOREIGN KEY (ChildID) REFERENCES Children 
--)


--CREATE TABLE SingleSittingAssignment
--(
--	SSAID	INT IDENTITY(1,1),
--	SPID	INTEGER,
--	DHBSSID INTEGER,

--	CONSTRAINT pk_SingleSittingAssignment PRIMARY KEY (SSAID),
--	FOREIGN KEY (SPID) REFERENCES SittingPlan ,
--	FOREIGN KEY (DHBSSID) REFERENCES DH_BSS 
--)


--CREATE TABLE Children_SingleSittingAssignment
--(
--	CSSAID	INT IDENTITY(1,1),
--	SSAID	INTEGER,
--	ChildID INTEGER,

--	CONSTRAINT pk_Children_SingleSittingAssignment PRIMARY KEY (CSSAID),
--	FOREIGN KEY (SSAID) REFERENCES SingleSittingAssignment ,
--	FOREIGN KEY (ChildID) REFERENCES Children 
--)



CREATE TABLE BabySitters_Languages
(
	BSLID		INT IDENTITY(1,1),
	BSID		INTEGER,
	LanguageID	INTEGER,

	CONSTRAINT pk_BabySitters_Languages PRIMARY KEY (BSLID),
	FOREIGN KEY (BSID) REFERENCES BabySitters,
	FOREIGN KEY (LanguageID) REFERENCES Languages 
)


CREATE TABLE Children_Languages
(
	CHLID		INT IDENTITY(1,1),
	ChildID		INTEGER,
	LanguageID	INTEGER,

	CONSTRAINT pk_Children_Languages PRIMARY KEY (CHLID),
	FOREIGN KEY (ChildID) REFERENCES Children,
	FOREIGN KEY (LanguageID) REFERENCES Languages 
)


CREATE TABLE HSs_BSs
(
	BSHSID	INT IDENTITY(1,1),
	BSID	INTEGER,
	HSID	INTEGER,

	CONSTRAINT pk_HSs_BSs PRIMARY KEY (BSHSID),
	FOREIGN KEY (BSID) REFERENCES BabySitters,
	FOREIGN KEY (HSID) REFERENCES HobbiesSkills 
)

CREATE TABLE Fees
(
	FeeID					INT IDENTITY(1,1),
	PlanID					INTEGER,
	AccountID				INTEGER,
	NoOfChildren			INTEGER,
	PlusOneRate				FLOAT(24),
	TotalPrice				MONEY,
	CairoSittersProfit		MONEY,
	BabySitterProfit		MONEY,
	
	CONSTRAINT pk_Fees PRIMARY KEY (FeeID),
	FOREIGN KEY (PlanID) REFERENCES Plans,
	FOREIGN KEY (AccountID) REFERENCES Accounts
)

CREATE TABLE Jobs
(
	JobID					INT IDENTITY(1,1),
	AccountID				INTEGER,
	BSID					INTEGER,
	FeeID					INTEGER,
	LanguageID				INTEGER,
	Location				varchar(50),
	AmountDue				INTEGER,
	PaidInAdvance			INTEGER,
	PaidInTotal				INTEGER,
	TotalHours		        INTEGER,
	RemainingHours			INTEGER,
	HoursPerMonth			INTEGER,
	TimesPerWeek			INTEGER,
	StartingDate			datetime,
	ExpectedEndDate			datetime,
	JobStatus				varchar(50),
	Comments				varchar(max),
	
	CONSTRAINT pk_Jobs PRIMARY KEY (JobID),
	FOREIGN KEY (AccountID) REFERENCES Accounts,
	FOREIGN KEY (BSID) REFERENCES BabySitters,
	FOREIGN KEY (FeeID) REFERENCES Fees,
    FOREIGN KEY (LanguageID) REFERENCES Languages
) 
	
--Other Levels End--

INSERT INTO Nationalities
  (NName)
    SELECT NName
      FROM  OPENROWSET(BULK  'C:\Users\Nour\Documents\Visual Studio 2010\Projects\CairoSitters\nationalities.csv',
      FORMATFILE='C:\Users\Nour\Documents\Visual Studio 2010\Projects\CairoSitters\ff.xml'  
       ) as t1 ;
GO


DROP TABLE HSs_BSs
DROP TABLE Children_Languages
DROP TABLE BabySitters_Languages
DROP TABLE EmergencyContacts_Accounts
DROP TABLE Jobs
DROP TABLE Plans
DROP TABLE BSEducationalBackground
DROP TABLE BSEmploymentBackground
DROP TABLE DH_BSS
DROP TABLE BSSchedule
DROP TABLE BSWorkConditions
DROP TABLE BabySitters
DROP TABLE Children
DROP TABLE Parents
DROP TABLE Accounts
DROP TABLE HobbiesSkills
DROP TABLE SchoolsUniversities
DROP TABLE Employers
DROP TABLE EmergencyContacts




